package lectores.parsersXLSX;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Set;
import lectores.LectorConvenios;
import org.xml.sax.helpers.DefaultHandler;
import lectores.ExcelMalFormadoException;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.SAXException;

public class XLSXHandler extends DefaultHandler {

  enum xssfDataType {

    BOOL,
    ERROR,
    FORMULA,
    INLINESTR,
    SSTINDEX,
    NUMBER,}
  StylesTable stylesTable;
  ReadOnlySharedStringsTable sharedStringsTable;
  boolean vIsOpen = false;
  int valoresLeidos = 0;
  xssfDataType nextDataType;
  short formatIndex;
  String formatString;
  int thisColumn = -1;
  int lastColumnNumber = -1;
  StringBuffer value;
  String sqlValues = "";
  LectorConvenios lector;
  int mesALeer;
  int renglonTratado = -1;
  int conteoCeldas = -1;
  int tipoFiltroPeriodo;
  HashMap<String, Integer> mesTrim;

  public XLSXHandler(StylesTable styles, ReadOnlySharedStringsTable strings, LectorConvenios lector, int mesALeer, int tipoFiltroPeriodo, HashMap<String, Integer> mesTrim) {
    this.stylesTable = styles;
    this.sharedStringsTable = strings;
    this.value = new StringBuffer();
    this.nextDataType = xssfDataType.NUMBER;
    this.lector = lector;
    this.mesALeer = mesALeer;
    this.tipoFiltroPeriodo = tipoFiltroPeriodo;
    this.mesTrim = mesTrim;
  }

  public void startElement(String uri, String localName, String name, Attributes attributes)
          throws SAXException {
    if ("inlineStr".equals(name) || "v".equals(name)) {
      vIsOpen = true;
      valoresLeidos++;
      value.setLength(0);
    } else if ("c".equals(name)) {
      valoresLeidos = 0;

      String r = attributes.getValue("r");
      int firstDigit = -1;
      for (int c = 0; c < r.length(); ++c) {
        if (Character.isDigit(r.charAt(c))) {
          firstDigit = c;
          break;
        }
      }

      thisColumn = nameToColumn(r.substring(0, firstDigit));

      this.nextDataType = xssfDataType.NUMBER;
      this.formatIndex = -1;
      this.formatString = null;
      String cellType = attributes.getValue("t");
      String cellStyleStr = attributes.getValue("s");
      if ("b".equals(cellType)) {
        nextDataType = xssfDataType.BOOL;
      } else if ("e".equals(cellType)) {
        nextDataType = xssfDataType.ERROR;
      } else if ("inlineStr".equals(cellType)) {
        nextDataType = xssfDataType.INLINESTR;
      } else if ("s".equals(cellType)) {
        nextDataType = xssfDataType.SSTINDEX;
      } else if ("str".equals(cellType)) {
        nextDataType = xssfDataType.FORMULA;
      } else if (cellStyleStr != null) {
        int styleIndex = Integer.parseInt(cellStyleStr);
        XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
        this.formatIndex = style.getDataFormat();
        this.formatString = style.getDataFormatString();
        if (this.formatString == null) {
          this.formatString = BuiltinFormats.getBuiltinFormat(this.formatIndex);
        }
      }
    }
  }

  public void endElement(String uri, String localName, String name)
          throws SAXException {
    String thisStr = "";

    if ("c".equals(name)) {
      conteoCeldas++;

      if (conteoCeldas < thisColumn) {
        int diferencia = thisColumn - conteoCeldas;
        if (valoresLeidos != 0) {
          String[] actualSQLValues = sqlValues.split("\\|");
          String ultimoValorLeido = actualSQLValues[actualSQLValues.length - 1];
          sqlValues = "";
          for (int i = 0; i < actualSQLValues.length - 1; i++) {
            sqlValues = sqlValues + actualSQLValues[i] + "|";
          }
          if ((conteoCeldas == 2 && thisColumn == 3) || (conteoCeldas == 12 && thisColumn == 13)) {
            sqlValues = sqlValues + "-1|";
          }
          for (int i = 0; i < diferencia; i++) {
            sqlValues = sqlValues + "0|";
          }
          sqlValues = sqlValues + ultimoValorLeido + "|";
        } else {
          if ((conteoCeldas == 2 && thisColumn == 3) || (conteoCeldas == 12 && thisColumn == 13)) {
            sqlValues = sqlValues + "-1|";
          }
          for (int i = 0; i < diferencia; i++) {
            sqlValues = sqlValues + "0|";
          }
        }
        conteoCeldas = thisColumn;
      }

      if (valoresLeidos == 0) {
        if (thisColumn == 2 || thisColumn == 12) {
          sqlValues = sqlValues + "-1|";
        } else {
          sqlValues = sqlValues + "0|";
        }
      }

    } else if ("v".equals(name)) {
      vIsOpen = false;

      switch (nextDataType) {

        case BOOL:
          char first = value.charAt(0);
          thisStr = first == '0' ? "FALSE" : "TRUE";
          break;

        case ERROR:
          thisStr = "\"ERROR:" + value.toString() + '"';
          break;

        case FORMULA:
          thisStr = '"' + value.toString() + '"';
          break;

        case INLINESTR:
          XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());
          thisStr = "'" + rtsi.toString() + "'";
          break;

        case SSTINDEX:
          String sstIndex = value.toString();
          try {
            int idx = Integer.parseInt(sstIndex);
            XSSFRichTextString rtss = new XSSFRichTextString(sharedStringsTable.getEntryAt(idx));
            thisStr = "'" + rtss.toString() + "'";
          } catch (NumberFormatException ex) {
            String cad = "Failed to parse SST index '" + sstIndex + "': " + ex.toString();
            System.exit(0);
          }
          break;

        case NUMBER:
          thisStr = value.toString();
          break;

        default:
          thisStr = "(TODO: Unexpected type: " + nextDataType + ")";
          break;
      }

      if (lastColumnNumber == -1) {
        lastColumnNumber = 0;
      }

      sqlValues = sqlValues + thisStr + "|";

      if (thisColumn > -1) {
        lastColumnNumber = thisColumn;
      }

    } else if ("row".equals(name)) {
      conteoCeldas = -1;
      renglonTratado++;

      int mes = 0;
      if (renglonTratado == 0) {
        sqlValues = "";
        lastColumnNumber = -1;
        return;
      } else {
        mes = validaMes(sqlValues.split("\\|")[0]);
      }
      if (mes == -1) {
        String tmpVal = "P_INV|";
        String[] splitVals = sqlValues.split("\\|");
        for (int j = 1; j < splitVals.length; j++) {
          tmpVal = tmpVal + splitVals[j] + "|";
        }
        sqlValues = tmpVal;

      } else {
        if (tipoFiltroPeriodo == LectorConvenios.FILTRO_BONOS_PRODUCCION) {
          if (mesALeer % 3 == 0) {
            int idPromo = Integer.parseInt(sqlValues.split("\\|")[12]);
            int idAgent = Integer.parseInt(sqlValues.split("\\|")[2]);
            
            boolean entraRegistro = false; //Esto es para no escluir los registros de promotor
            
            Set<String> llaves = mesTrim.keySet();
            Iterator ite = llaves.iterator();
            Integer mestrim = null;
            
            while(ite.hasNext()) {
              String llaveAct = (String) ite.next();
              if(llaveAct.split("\\.")[0].equals("" + idPromo)) {
                entraRegistro = true;
                mestrim =  mesTrim.get(llaveAct);
              }
              if(llaveAct.split("\\.")[1].equals("" + idAgent)) {
                entraRegistro = true;
                mestrim =  mesTrim.get(llaveAct);
              }
            }
            
            entraRegistro = entraRegistro && (mestrim != null);

            if (entraRegistro) {
              if (mestrim == 1) {
                if (!(mesALeer - 2 <= mes && mes <= mesALeer)) {
                  sqlValues = "";
                  lastColumnNumber = -1;
                  return;
                }
              } else {
                if (mes != mesALeer) {
                  sqlValues = "";
                  lastColumnNumber = -1;
                  return;
                }
              }

            } else {
              sqlValues = "";
              lastColumnNumber = -1;
              return;
            }
          } else {
            if (mes != mesALeer) {
              sqlValues = "";
              lastColumnNumber = -1;
              return;
            }
          }
        } else if (tipoFiltroPeriodo == LectorConvenios.FILTRO_BONOS_CALIDAD) {
          if (mes > mesALeer) {
            sqlValues = "";
            lastColumnNumber = -1;
            return;
          }
        }

      }
      try {
        lector.insertaRegistro(sqlValues, renglonTratado + 1);
        System.out.println(sqlValues);
      } catch (SQLException sqle) {
        sqle.printStackTrace();
        System.exit(0);
      } catch (ExcelMalFormadoException emfe) {
        throw new XLSXException(emfe);
      }

      sqlValues = "";
      lastColumnNumber = -1;
    }
  }

  public void characters(char[] ch, int start, int length)
          throws SAXException {
    if (vIsOpen) {
      value.append(ch, start, length);
    }
  }

  int nameToColumn(String name) {
    int column = -1;
    for (int i = 0; i < name.length(); ++i) {
      int c = name.charAt(i);
      column = (column + 1) * 26 + c - 'A';
    }
    return column;
  }

  int validaMes(String mesStr) {
    int mes = -1;
    if (mesStr == null || mesStr.equals("") || mesStr.replaceAll("'", "").trim().equals("")) {
      return -1;
    }

    String[] mesSplit = mesStr.replaceAll("'", "").split("-");
    if (mesSplit.length != 2) {
      return -1;
    } else if (mesSplit[0].length() != 4 && mesSplit[1].length() != 2) {
      return -1;
    }

    try {
      Integer.parseInt(mesSplit[1]);
    } catch (Exception e) {
      return -1;
    }

    mes = Integer.parseInt(mesSplit[1]);
    return mes;
  }
}
